package com.zglc.ms.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

@Repository
public class TransDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Integer save(int userId, long orderNo, int shopProductId, String priceDetail, int cnt,
			String payDetail, String payChannel, int platfrom, boolean isNeedSend, String remark, 
			int sendAddressId, String address, String phone, String contact) {
		final String sql = "INSERT INTO shop_trans (user_id, shop_product_id, price_detail, good_cnt, pay_detail, "
				+ "pay_channel, platfrom, is_need_send, order_no, remark, send_address_id, "
				+ "address, phone, contact, created_at, updated_at) VALUES (?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, "
				+ " ?, ?, ?, NOW(), NOW())";

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, userId);
				ps.setInt(2, shopProductId);
				ps.setString(3, priceDetail);
				ps.setInt(4, cnt);
				ps.setString(5, payDetail);
				
				ps.setString(6, payChannel);
				ps.setInt(7, platfrom);
				ps.setBoolean(8, isNeedSend);
				ps.setLong(9, orderNo);
				ps.setString(10, remark);
				ps.setInt(11, sendAddressId);
				ps.setString(12, address);
				ps.setString(13, phone);
				ps.setString(14, contact);
				return ps;
			}
		}, keyHolder);

		return keyHolder.getKey().intValue();
	}

	public int sumSuccess(int userId, int shopProductId) {
		final String sql = "SELECT SUM(good_cnt) FROM shop_trans WHERE user_id = ? AND shop_product_id = ? AND state = 2";
		try {
			Integer count = jdbcTemplate.queryForObject(sql, new Object[] { userId, shopProductId }, Integer.class);
			return count == null ? 0 : count;
		} catch (Exception e) {
		}

		return 0;
	}

	public int sumSuccess(int userId, int shopProductId, String yearMonthStr) {
		final String sql = "SELECT SUM(good_cnt) FROM shop_trans WHERE user_id = ? AND shop_product_id = ? AND state = 2 "
				+ " AND DATE_FORMAT(success_time, '%Y-%m') = ?";
		try {
			Integer count = jdbcTemplate.queryForObject(sql, new Object[] { userId, shopProductId, yearMonthStr },
					Integer.class);
			return count == null ? 0 : count;
		} catch (Exception e) {
		}

		return 0;
	}

	public boolean updateState(int shopTransId, int state) {
		final String sql = "UPDATE shop_trans SET state = ? WHERE id = ?";
		try {
			Integer count = jdbcTemplate.update(sql, state, shopTransId);
			return count != null;
		} catch (Exception e) {
		}

		return false;
	}
	
	public boolean updateSuccessState(int shopTransId, String payOrderNo) {
		final String sql = "UPDATE shop_trans SET state = 2, from_trans_id = ?, success_time = NOW() WHERE id = ?";
		try {
			Integer count = jdbcTemplate.update(sql, payOrderNo, shopTransId);
			return count != null;
		} catch (Exception e) {
		}

		return false;
	}
	
	public boolean updateCancel(int shopTransId) {
		final String sql = "UPDATE shop_trans SET state = 5, updated_at = NOW() WHERE id = ?";
		try {
			Integer count = jdbcTemplate.update(sql, shopTransId);
			return count != null;
		} catch (Exception e) {
		}

		return false;
	}

	public TransModel get(String orderNo) {
		final String sql = "SELECT * FROM shop_trans WHERE order_no = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new Object[]{orderNo}, new RowMapper<TransModel>() {

				@Override
				public TransModel mapRow(ResultSet rs, int rowNum) throws SQLException {
					return change(rs);
				}

			});
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
		
	}
	
	public TransModel get(int transId) {
		final String sql = "SELECT * FROM shop_trans WHERE id = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new Object[]{transId}, new RowMapper<TransModel>() {

				@Override
				public TransModel mapRow(ResultSet rs, int rowNum) throws SQLException {
					return change(rs);
				}
			});
			
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}
	
	public List<TransModel> getByUserId(int userId, int page, int pageNum) {
		final String sql = "SELECT * FROM shop_trans WHERE user_id = ? ORDER BY state, created_at DESC "
				+ "  LIMIT " + pageNum*(page -1) + "," + pageNum;
		try {
			return jdbcTemplate.query(sql, new Object[]{userId}, new RowMapper<TransModel>() {

				@Override
				public TransModel mapRow(ResultSet rs, int rowNum) throws SQLException {
					return change(rs);
				}
			});
			
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}
	
	public Integer getByUserIdCnt(int userId) {
		final String sql = "SELECT count(1) cnt FROM shop_trans WHERE user_id = ? ";
		try {
			Integer count = jdbcTemplate.query(sql.toString(), new Object[]{userId}, (rs) -> {
				Integer i = null;
				while (rs.next()) {
					i = rs.getInt("cnt");
				}
				return i;
			});
			return count;
		} catch (DataAccessException e) {
			logger.info(e.getLocalizedMessage());
		}
		return null;
	}
	
	private TransModel change(ResultSet rs) throws SQLException{
		TransModel model = new TransModel();
		model.setId(rs.getInt("id"));
		model.setOrderNo(rs.getLong("order_no"));
		model.setUserId(rs.getInt("user_id"));
		model.setShopProductId(rs.getInt("shop_product_id"));
		model.setGoodCnt(rs.getInt("good_cnt"));
		model.setState(rs.getInt("state"));
		model.setPriceDetail(rs.getString("price_detail"));
		model.setPayDetail(rs.getString("pay_detail"));
		model.setCreatedAt(rs.getTimestamp("created_at"));
		model.setRemark(rs.getString("remark"));
		model.setNeedSend(rs.getBoolean("is_need_send"));
		model.setSend(rs.getBoolean("is_send"));
		model.setSendAddressId(rs.getInt("send_address_id"));
		model.setAddress(rs.getString("address"));
		model.setPhone(rs.getString("phone"));
		model.setContact(rs.getString("contact"));
		return model;
	}

	public static class TransModel {
		private int id;
		private long orderNo;
		private int userId;
		private int shopProductId;
		private int goodCnt;
		private int state;
		private String payDetail;
		private Date createdAt;
		private String remark;
		private boolean isNeedSend;
		private boolean isSend;
		private int sendAddressId;
		private String address;
		private String phone;
		private String contact;
		private String priceDetail;
		
		public String getPriceDetail() {
			return priceDetail;
		}

		public void setPriceDetail(String priceDetail) {
			this.priceDetail = priceDetail;
		}

		public String getPhone() {
			return phone;
		}

		public void setPhone(String phone) {
			this.phone = phone;
		}

		public String getContact() {
			return contact;
		}

		public void setContact(String contact) {
			this.contact = contact;
		}

		public String getAddress() {
			return address;
		}

		public void setAddress(String address) {
			this.address = address;
		}

		public int getId() {
			return id;
		}

		public void setId(int id) {
			this.id = id;
		}

		public int getUserId() {
			return userId;
		}

		public void setUserId(int userId) {
			this.userId = userId;
		}

		public int getShopProductId() {
			return shopProductId;
		}

		public void setShopProductId(int shopProductId) {
			this.shopProductId = shopProductId;
		}

		public int getGoodCnt() {
			return goodCnt;
		}

		public void setGoodCnt(int goodCnt) {
			this.goodCnt = goodCnt;
		}

		public int getState() {
			return state;
		}

		public void setState(int state) {
			this.state = state;
		}

		public Date getCreatedAt() {
			return createdAt;
		}

		public void setCreatedAt(Date createdAt) {
			this.createdAt = createdAt;
		}
		
		public long getOrderNo() {
			return orderNo;
		}

		public void setOrderNo(long orderNo) {
			this.orderNo = orderNo;
		}
		
		public String getRemark() {
			return remark;
		}

		public void setRemark(String remark) {
			this.remark = remark;
		}
		
		public boolean isNeedSend() {
			return isNeedSend;
		}

		public void setNeedSend(boolean isNeedSend) {
			this.isNeedSend = isNeedSend;
		}
		
		public boolean isSend() {
			return isSend;
		}

		public void setSend(boolean isSend) {
			this.isSend = isSend;
		}

		public int getSendAddressId() {
			return sendAddressId;
		}

		public void setSendAddressId(int sendAddressId) {
			this.sendAddressId = sendAddressId;
		}

		public String getPayDetail() {
			return payDetail;
		}

		public void setPayDetail(String payDetail) {
			this.payDetail = payDetail;
		}

		@Override
		public String toString() {
			return "TransModel [id=" + id + ", orderNo=" + orderNo + ", userId=" + userId + ", shopProductId="
					+ shopProductId + ", goodCnt=" + goodCnt + ", state=" + state + ", payDetail=" + payDetail
					+ ", createdAt=" + createdAt + ", remark=" + remark + ", isNeedSend=" + isNeedSend + ", isSend="
					+ isSend + ", sendAddressId=" + sendAddressId + ", address=" + address + ", phone=" + phone
					+ ", contact=" + contact + ", priceDetail=" + priceDetail + "]";
		}

	}

}